﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Linq;
using System.Globalization;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Web.Hosting;
using EOrder_Entities;
using Finisar.SQLite;
using log4net;		
using log4net.Config;

namespace EOrder_WCF
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "EOrderService" in code, svc and config file together.
    public class EOrderService : System.Web.UI.Page, IService1 
    {
        private static SQLiteConnection sql_con;
        private static SQLiteCommand sql_cmd;
        private static SQLiteDataAdapter DB;
        private static DataSet DS = new DataSet();
        private static DataTable DT = new DataTable();
        protected static readonly ILog log = LogManager.GetLogger(typeof(EOrderService));

        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }

        public CompositeType GetDataUsingDataContract(CompositeType composite)
        {
            if (composite == null)
            {
                throw new ArgumentNullException("composite");
            }
            if (composite.BoolValue)
            {
                composite.StringValue += "Suffix";
            }
            return composite;
        }

        public Table[] GetTables()
        {
            var db = new EOrderDataContext();

            return db.Tables.ToArray();
        }

        /// <summary>
        /// Create new order
        /// </summary>
        /// <author>DaoLA</author>
        /// <param name="order"></param>
        /// <returns></returns>
//        public bool CreateOrder(Order order)
//        {
//            if (order == null) return false;
//
//            var db = new EOrderDataContext();
//            
//            try
//            {
//                var table = db.Tables.SingleOrDefault(p => p.Table_Id == order.Table_Id);
//                db.Orders.InsertOnSubmit(order);
//                db.SubmitChanges();
//                table.takeOrder(order);
//                db.SubmitChanges();
//            }
//            catch (Exception e)
//            {
//                return false;
//            }
//
//            return true;
//        }

        public bool CreateOrder(string orderDetailString, string orderItemListString)
        {
            
            try
            {
                var provider = CultureInfo.InvariantCulture;
//                orderDetailString = "0,6,O,7,5,0,0,no,12/23/2012,12/23/2012";
                var orderDetailValues = orderDetailString.Split(',');
                String date = orderDetailValues[8];
                DateTime Created_Date = Convert.ToDateTime(orderDetailValues[8]);

                var order = new Order
                {
                    Order_Id = Convert.ToInt32(orderDetailValues[0]),
                    Table_Id = Convert.ToInt32(orderDetailValues[1]),
                    Order_Status_Code = Convert.ToChar(orderDetailValues[2]),
                    Priority = Convert.ToInt32(orderDetailValues[3]),
                    Number_of_Customer = Convert.ToInt32(orderDetailValues[4]),
                    Discount_Amount = Convert.ToDouble(orderDetailValues[5]),
                    Other_Fee = Convert.ToDouble(orderDetailValues[6]),
                    Other_Fee_Description = orderDetailValues[7],
                    Created_Date = Convert.ToDateTime(orderDetailValues[8]),
                    Closed_Date = null//Convert.ToDateTime(orderDetailValues[9]),
                };

//                orderItemListString = "0,0,1,2,N,normal,12/23/2012,12/23/2012;0,0,2,1,N,normal,12/23/2012,12/23/2012";
                var orderItems = orderItemListString.Split(';');
                int size = orderItems.Length - 1;
                for (int i = 0; i < size; i++)
                {
                    var orderItemValues = orderItems[i].Split(',');


                    var orderItem = new OrderItem
                    {
                        Order_Item_Id = Convert.ToInt32(orderItemValues[0]),
                        Order_Id = Convert.ToInt32(orderItemValues[1]),
                        Dish_Id = Convert.ToInt32(orderItemValues[2]),
                        Quantity = Convert.ToInt32(orderItemValues[3]),
                        Order_Item_Status_Code = Convert.ToChar(orderItemValues[4]),
                        Note = orderItemValues[5],
                        Created_Date = Convert.ToDateTime(orderItemValues[6]),
                        Last_Updated_Date = Convert.ToDateTime(orderItemValues[7])
                    };
                    order.OrderItems.Add(orderItem);
                }

                var db = new EOrderDataContext();

                try
                {
                    var table = db.Tables.SingleOrDefault(p => p.Table_Id == order.Table_Id);
                    db.Orders.InsertOnSubmit(order);
                    db.SubmitChanges();
                    table.takeOrder(order);
                    db.SubmitChanges();
                }
                catch (Exception e)
                {
                    return false;
                }
            }
            catch (Exception e)
            {

                return false;
            }
            

            return true;
        }

        public Order GetOrder(int tableId)
        {
            var db = new EOrderDataContext();

            Order order = null;
            try
            {
                order = db.Orders.SingleOrDefault(p => p.Table_Id == tableId && p.Closed_Date == null);
                order.OrderItems.ToArray();
                order.Feedbacks.ToArray();
            }
            catch(Exception e)
            {
                return null;
            }

            return order;
        }

        public bool UpdateOrder(Order order)
        {
            if (order == null) return false;

            var db = new EOrderDataContext();

            try
            {
                db.Orders.Attach(order);
                db.Feedbacks.AttachAll(order.Feedbacks.ToArray());
                db.OrderItems.AttachAll(order.OrderItems.ToArray());
                db.Refresh(RefreshMode.KeepCurrentValues, order);
                db.Refresh(RefreshMode.KeepCurrentValues, order.OrderItems.ToArray());
                db.Refresh(RefreshMode.KeepCurrentValues, order.Feedbacks.ToArray());
                db.SubmitChanges();
            }
            catch (Exception e)
            {
                return false;
            }

            return true;
        }

        public bool GroupOrders(int[] orderIds)
        {
            if (orderIds.Length < 2) return false;

            var db = new EOrderDataContext();

            try
            {
                for (int i = 1; i < orderIds.Length; i++)
                {
                    var order = db.Orders.SingleOrDefault(p => p.Order_Id == orderIds[i] && p.Closed_Date == null);
                    foreach (var orderItem in order.OrderItems)
                    {
                        orderItem.Order_Id = orderIds[0];
                    }
                    order.Closed_Date = DateTime.Now;
                    order.Table.free();
                }

                db.SubmitChanges();
            }
            catch(Exception e)
            {
                return false;
            }
            return true;
        }

//        public bool UpdateOrderItems(List<OrderItem> orderItems)
//        {
//            if (orderItems == null) return false;
//
//            var db = new EOrderDataContext();
//
//            try
//            {
//                db.OrderItems.AttachAll(orderItems);
//                db.Refresh(RefreshMode.KeepCurrentValues, orderItems);
//                db.SubmitChanges();
//            }
//            catch (Exception e)
//            {
//                return false;
//            }
//            return true;
//        }

        public bool AddOrderItems(List<OrderItem> orderItems)
        {
            if (orderItems == null) return false;

            var db = new EOrderDataContext();

            try
            {
                db.OrderItems.InsertAllOnSubmit(orderItems);
                db.SubmitChanges();
            }
            catch (Exception e)
            {
                return false;
            }
            return true;
        }

        public bool RequestBill(int orderId)
        {
            throw new NotImplementedException();
        }

        public bool ChangeTable(int oldTableId, int newTableId)
        {
            var db = new EOrderDataContext();

            var currentTable = db.Tables.SingleOrDefault(p => p.Table_Id == oldTableId);
            var newTable = db.Tables.SingleOrDefault(p => p.Table_Id == newTableId);

            if (currentTable == null || newTable == null) return false;

            if (newTable.Current_Order_Id == null)
            {
                newTable.takeOrder(currentTable.Orders[0]);
                currentTable.free();

                try
                {
                    db.SubmitChanges();
                }
                catch (Exception e)
                {
                    return false;
                }
            }
            
            return true;
        }

        public bool GroupTables(int mainTableId, List<int> otherTableIds)
        {
            var db = new EOrderDataContext();

            var mainTable = db.Tables.SingleOrDefault(p => p.Table_Id == mainTableId);
            foreach (var tableId in otherTableIds)
            {
                var otherTable = db.Tables.SingleOrDefault(p => p.Table_Id == tableId);
                GroupOrders(new[] {Convert.ToInt32(mainTable.Current_Order_Id), 
                    Convert.ToInt32(otherTable.Current_Order_Id)});
                otherTable.Group_Number = mainTableId;
                otherTable.Table_Status_Code = 'F';
            }

            try
            {
                db.SubmitChanges();
            }
            catch (Exception e)
            {
                return false;
            }

            return true;
        }

        public bool UnGroupTables(int groupNumber)
        {
            var db = new EOrderDataContext();

            var tableList = db.Tables.Where(p => p.Group_Number == groupNumber && p.Table_Id != groupNumber);

            foreach (var table in tableList)
            {
                table.free();
            }

            try
            {
                db.SubmitChanges();
            }
            catch (Exception e)
            {
                return false;
            }

            return true;
        }

        public bool SendFeedback(int orderId, string comment)
        {
            var db = new EOrderDataContext();

            var feedback = new Feedback { Order_Id = orderId, Comment = comment };
            db.Feedbacks.InsertOnSubmit(feedback);

            try
            {
                db.SubmitChanges();
            }
            catch (Exception e)
            {
                return false;
            }

            return true;
        }

        #region Manipulate on SQLLite - PhatNT 20/3/2012

        private void SetConnection()
        {
           // string path = @"C:\inetpub\wwwroot\HostEorder\EOrder.db";
            string path = HostingEnvironment.MapPath(@"~\SQLLiteDB\EOrder.db");
           // string a = Server.MapPath(@"~\SQLLiteDB\EOrder.db");
            sql_con = new SQLiteConnection("Data Source= " + path + ";Version=3;New=False;Compress=True;");
            XmlConfigurator.Configure();
            log.Debug("Qua duoc setconnection()");
        }

        private void ExecuteQuery(string txtQuery)
        {
            try
            {
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = txtQuery;

                sql_cmd.ExecuteNonQuery();
                sql_con.Close();
            }
            catch (Exception ex)
            {
                XmlConfigurator.Configure();
                log.Error("Loi", ex);
                throw ex;
            }
        }

        private void Add(List<Category> categories, List<Dish> dishes, List<DishCategory> dishCategories, List<Table> tables)
        {
            var txtSqlQuery = new StringBuilder();
            foreach (var category in categories)
            {
                txtSqlQuery.Append("insert into Categories values (" + category.Category_Id + ",'" + category.Name + "'," + (category.IsActive ? 1 : 0) + ",'" + category.Category_Image_Path + "');");
            }

            foreach (var dish in dishes)
            {
                txtSqlQuery.Append("insert into Dishes values (" + dish.Dish_Id + ",'" + dish.Name + "','" + dish.Description + "','" + dish.Price + "','" + dish.Dish_Image_Path + "'," + (dish.IsActive ? 1 : 0) + "," + (dish.IsNew ? 1 : 0) + ");");
            }

            foreach (var dishCategory in dishCategories)
            {
                txtSqlQuery.Append("insert into Dish_Categories values (" + dishCategory.Dish_Category_Id + ",'" + dishCategory.Dish_Id + "','" + dishCategory.Category_Id + "');");
            }

            foreach (var table in tables)
            {
                txtSqlQuery.Append("insert into Tables values (" + table.Table_Id + ",'" + table.Table_Status_Code + "','" + table.Current_Order_Id + "','" + table.Group_Number + "');");
            }

            ExecuteQuery(txtSqlQuery.ToString());

        }

        private void DeleteAll()
        {
            string txtSqlQuery = "Delete From Categories; Delete From Dishes; Delete From Dish_Categories; Delete From Tables";
            ExecuteQuery(txtSqlQuery);
        }

        #endregion

        //Get Categories - PhatNT 20/3/2012
        private List<Category> GetCategories()
        {
            var db = new EOrderDataContext();
            var categories = from p in db.Categories
                             select p;
            return new List<Category>(categories);
        }

        //Get Dishes - PhatNT 20/3/2012
        private List<Dish> GetDishes()
        {
            var db = new EOrderDataContext();
            var dishes = from p in db.Dishes
                         select p;
            return new List<Dish>(dishes);
        }

        //Get Dishes_Categories - PhatNT 20/3/2012
        private List<DishCategory> GetDishCategory()
        {
            var db = new EOrderDataContext();
            var dishcategory = from p in db.DishCategories
                               select p;
            return new List<DishCategory>(dishcategory);
        }

        //Get Table - PhatNT 20/3/2012
        private List<Table> GetTable()
        {
            var db = new EOrderDataContext();
            var tables = from p in db.Tables
                         select p;
            return new List<Table>(tables);
        }

        /// <summary>
        /// Sync data from server to tablet
        /// </summary>
        /// <author>PhatNT</author>
        /// <createdDate>2012/03/20</createdDate>
        /// <returns>string</returns>
        public string SyncData()
        {
            //Demo Use log4net		
	            XmlConfigurator.Configure();		
	            log.Debug("Toi la Nguyen Thanh Phat");
            List<Category> categories = GetCategories();
            List<Dish> dishes = GetDishes();
            List<DishCategory> dishCategories = GetDishCategory();
            List<Table> tables = GetTable();
            DeleteAll();
            Add(categories, dishes, dishCategories, tables);
            return ConfigurationManager.AppSettings["LinkDownDBSQLlite"];

        }

        

    }
}

